/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import action.report.CostProduct;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;

/**
 *
 * @author Trang
 */
public class ReportDAO {
    Connection con;
    public ReportDAO()
    {
         con = DBFactory.createConnection("onlineshopping");
    }
    
    public List<Revenuebyproduct> getRevenueByProductBetweenDate(Date starttime, Date endtime)
    {
        List<Revenuebyproduct> results = new ArrayList<Revenuebyproduct>();
        try
        {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String date1 = dateFormat.format(starttime);
            String date2 = dateFormat.format(endtime);
            //System.out.println(newDate);
//            SELECT Product_idProduct,orderPrice,orderQuantity, createtime FROM onlineshopping.revenuebyproduct 
//Where createtime between '2013-10-02' and '2013-10-31';
            String sql ="SELECT * FROM revenuebyproduct WHERE createTime BETWEEN ? AND ?";
            
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setDate(1, new java.sql.Date (starttime.getTime()));
            ps.setDate(2, new java.sql.Date (endtime.getTime()));
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                Date createtime = rs.getDate("createtime"); 
                double orderPrice = rs.getDouble("orderPrice");
                int orderQuantity = rs.getInt("orderQuantity");
                
                Product order = new ProductDAO().getProductShortFromProductId(Product_idProduct);
  
                Revenuebyproduct model = new Revenuebyproduct(order, createtime, orderQuantity, orderPrice);
                
                results.add(model);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
    
    public List<Revenuebyproduct> getRevenueGroupbyProduct(Date starttime, Date endtime)
    {
        List<Revenuebyproduct> results = new ArrayList<Revenuebyproduct>();
        try
        {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String date1 = dateFormat.format(starttime);
            String date2 = dateFormat.format(endtime);
            String sql ="SELECT Product_idProduct,orderPrice, "
                    + "sum(orderQuantity) as totalQuantity, "
                    + "sum(orderQuantity*orderPrice) as totalPrice "
                    + "FROM revenuebyproduct WHERE createTime BETWEEN ? AND ?"
                    + "group by Product_idProduct "
                    + "Order by Product_idProduct";
            //System.out.println(sql);
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setDate(1, new java.sql.Date (starttime.getTime()));
            ps.setDate(2, new java.sql.Date (endtime.getTime()));
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                double orderPrice = rs.getDouble("orderPrice");
                int totalQuantity = rs.getInt("totalQuantity");
                
                double totalPrice = rs.getDouble("totalPrice");
                
                Product order = 
                        new ProductDAO().getProductShortFromProductId(Product_idProduct);
  
                Revenuebyproduct model = new Revenuebyproduct(order, orderPrice, totalQuantity, totalPrice);
                
                results.add(model);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
    public List<Revenuebyproduct> getRevenueGroupbyProduct()
    {
        List<Revenuebyproduct> results = new ArrayList<Revenuebyproduct>();
        try
        {    
            String sql ="SELECT Product_idProduct,orderPrice, "
                    + "sum(orderQuantity) as totalQuantity, "
                    + "sum(orderQuantity*orderPrice) as totalPrice "
                    + "FROM revenuebyproduct"
                    + "group by Product_idProduct "
                    + "Order by Product_idProduct";
            //System.out.println(sql);
            PreparedStatement ps = con.prepareStatement(sql);
          
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                double orderPrice = rs.getDouble("orderPrice");
                int totalQuantity = rs.getInt("totalQuantity");
                
                double totalPrice = rs.getDouble("totalPrice");
                
                Product order = new ProductDAO().getProductShortFromProductId(Product_idProduct);
  
                Revenuebyproduct model = new Revenuebyproduct(order, orderPrice, totalQuantity, totalPrice);
                
                results.add(model);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
    
    public List<CostProduct> GetReceipt(Date starttime, Date endtime)
    {
        List<CostProduct> results = new ArrayList<CostProduct>();
        try
        {    
            String sql ="SELECT receiptitem.Product_idProduct, receiptitem.receiptQuantity," 
                        +"receiptitem.receiptPrice," 
                        +"receiptitem.receiptPrice * receiptitem.receiptQuantity as totalCost," 
                        +"receipt.createTime" 
                        +" FROM onlineshopping.receiptitem, receipt " 
                        +"WHERE " 
                        +"receipt.idReceipt = receiptitem.Receipt_idReceipt "
                        +"AND receipt.createTime Between ? AND ?";
            //System.out.println(sql);
            PreparedStatement ps = con.prepareStatement(sql);
          
            ps.setDate(1, new java.sql.Date (starttime.getTime()));
            ps.setDate(2, new java.sql.Date (endtime.getTime()));
            
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                Date createTime = rs.getDate("createTime");
                double totalCost = rs.getDouble("totalCost");
                double receiptPrice = rs.getDouble("receiptPrice");
                int Quantity = rs.getInt("receiptQuantity");
                
                Product order = new ProductDAO().getProductShortFromProductId(Product_idProduct);
  
                CostProduct model = new CostProduct(order, createTime, totalCost, receiptPrice, Quantity);
                
                results.add(model);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ReportDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }
}
